<?php

class Default_Model_Yatzy_DbTable_Result extends Zend_Db_Table_Abstract
{
    protected $_name    = 'spagooy_result';
	protected $_primary = 'spagooy_result_id';
	
	public function fetchTopTen()
	{
		try
		{
			$query = $this->select();
			$query->from(array('r' => 'spagooy_result'), array(
				'r.spagooy_result_id',
				'r.spagooy_result_total',
				'r.spagooy_result_user_id',
				'u.spagooy_user_first_name',
				'u.spagooy_user_last_name',
				'u.spagooy_user_email'
			));
			$query->join(array('u' => 'spagooy_user'), 'u.spagooy_user_id = r.spagooy_result_user_id' ,array());
			$query->order('r.spagooy_result_total DESC');
			$query->limit(10);

			$query->setIntegrityCheck(false);
			$results = $this->fetchAll($query);
			return $results;
		}
		
		catch (Exception $e)
		{
			throw new Exception("Could not fetchTopTen(). Exception = $e");
		}
	}
	
	public function fetchTopTenPlayers()
	{
		try
		{
			$query = $this->select();
			$query->from(array('r' => 'spagooy_result'), array(
				'r.spagooy_result_id',
				'r.spagooy_result_total',
				'r.spagooy_result_user_id',
				'u.spagooy_user_first_name',
				'u.spagooy_user_last_name',
				'u.spagooy_user_email'
			));
			$query->join(array('u' => 'spagooy_user'), 'u.spagooy_user_id = r.spagooy_result_user_id' ,array('best_res'=>'MAX(r.spagooy_result_total)'));
			$query->group('r.spagooy_result_user_id');
			$query->order('best_res DESC');
			$query->limit(10);

			$query->setIntegrityCheck(false);
			$results = $this->fetchAll($query);
			return $results;
		}
		
		catch (Exception $e)
		{
			throw new Exception("Could not fetchTopTen(). Exception = $e");
		}
	}
	
	public function fetchTopAverage()
	{
		try
		{
			$db = new Zend_Db_Adapter_Mysqli(array(
   							'host'     => 'mysql.spagooy.com',
						    'username' => 'web37108_hikuser',
						    'password' => '1!AssssQ',
						    'dbname'   => 'web37108_hik'
						));
						
			$sql = 'SELECT u.spagooy_user_id,
					u.spagooy_user_first_name,
					u.spagooy_user_last_name,
					u.spagooy_user_email,
					AVG(r.spagooy_result_total) as average
					FROM spagooy_result r
					JOIN spagooy_user u
					ON u.spagooy_user_id = r.spagooy_result_user_id
					GROUP BY u.spagooy_user_id
					ORDER BY average DESC
					LIMIT 10				
			';
			
			$results = $db->fetchAll($sql);
			
	        return $results;
		
		}
		catch (Exception $e)
		{
			throw new Exception("Could not fetchAverage(). Exception = $e");
		}
	}
	
	public function fetchGamesPlayed()
	{
		try
		{
			$db = new Zend_Db_Adapter_Mysqli(array(
   							'host'     => 'mysql.spagooy.com',
						    'username' => 'web37108_hikuser',
						    'password' => '1!AssssQ',
						    'dbname'   => 'web37108_hik'
						));
						
			$sql = 'SELECT r.spagooy_result_user_id,
					       COUNT(r.spagooy_result_id) as games,
					       u.spagooy_user_first_name,
					       u.spagooy_user_last_name,
					       u.spagooy_user_email
					
					FROM spagooy_result r
					JOIN spagooy_user u
					ON r.spagooy_result_user_id = u.spagooy_user_id
					GROUP BY r.spagooy_result_user_id
					ORDER BY games DESC
					LIMIT 10;			
			';
			
			$results = $db->fetchAll($sql);
			
	        return $results;
		
		}
		catch (Exception $e)
		{
			throw new Exception("Could not fetch games played(). Exception = $e");
		}
	}
	
	public function hasUnfinnishedGame($userId)
	{
		try
		{
			$query = $this->select();
			$query->from(array('r' => 'spagooy_result'), array(
				'r.spagooy_result_id'
			));
			$query->join(array('u' => 'spagooy_user'), 'u.spagooy_user_id = r.spagooy_result_user_id' ,array());
			$query->where('spagooy_result_user_id = ?', $userId);
			$query->where('spagooy_result_finnished = ?', false);

			$query->setIntegrityCheck(false);
			$user = $this->fetchAll($query);
			return $user[0]['spagooy_result_id'];
		}
		catch(exception $e)
		{
			throw new Exception("Could not hasUnfinnishedGame(). Exception = $e");			
		}
	}
	
	public function getUnfinnishedGame($userId)
	{
		
		
	}
}
